# 数据库工具类
import datetime
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base


def get_db_url():
    dbinfo = {
        "ENGINE": "mysql",
        "DRIVER": "pymysql",
        "USER": "zz-flask-test1",
        "PASSWORD": "Zx3fcpyCyceWrARp",  # 123456
        "HOST": "47.118.40.79",
        "PORT": "3306",
        "DBNAME": "zz-flask-test1"
    }
    engine = dbinfo.get("ENGINE") or "mysql"
    driver = dbinfo.get("DRIVER") or "pymysql"
    user = dbinfo.get("USER") or ""
    password = dbinfo.get("PASSWORD") or ""
    host = dbinfo.get("HOST") or ""
    port = dbinfo.get("PORT") or ""
    dbname = dbinfo.get("DBNAME") or ""
    return "{}+{}://{}:{}@{}:{}/{}?charset=utf8mb4".format(engine, driver, user, password, host, port, dbname)


# 服务器  数据库----------------------------------------------------------------------------------------------------------
engine = create_engine(get_db_url(),
                       pool_size=20,  # 连接池大小
                       pool_recycle=120,  # 多久之后对线程池中的线程进行一次连接的回收（重置）
                       pool_pre_ping=True,  # 连接断开是否自动重连
                       max_overflow=15  # 最大值上限
                       )

db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))

Base = declarative_base()
Base.query = db_session.query_property()
# 增加事务
Base.begin_nested = db_session.begin_nested()


# 数据库加载  创建表
# 当有新表建立的时候  可以直接放在下面建表  需要建表的类要直接继承 Base  不继承的话就需要在一个文件  且有间接继承关系
def init_db():
    # import all modules here that might define models so that
    # they will be registered properly on the metadata.  Otherwise
    # you will have to import them first before calling init_db()
    # 导入模型并建表
    Base.metadata.create_all(bind=engine)


# 字典的 基类
class BntTableBase(Base):
    __abstract__ = True

    # 插入数据
    def save(self):
        db_session.add(self)
        db_session.commit()
        return self

    # 转换成字典
    def to_dict(self):
        value = {}
        for column in self.__table__.columns:
            attribute = getattr(self, column.name)
            if isinstance(attribute, datetime.datetime):
                attribute = str(attribute)
            value[column.name] = attribute
        return value

    def from_dict(self, attributes):
        for attribute in attributes:
            setattr(self, attribute, attributes[attribute])
        return self


# 结果集 results 列表转字典
def model_to_dict(results):
    if type(results) == list:
        v = []
        for ven in results:
            v.append(ven.to_dict())
        return v
    else:
        return results.to_dict()
